-- @owner: @zou_jialiang050
-- @date: 2022/02/24
-- @testpoint: 在更新src视图前更新src表，同时以触发函数代替触发条件向des表中插入数据

--step1:建表;expect:成功
drop table if exists t_trigger_src cascade;
drop table if exists t_trigger_des cascade;
create table t_trigger_src(id1 int, id2 int, id3 int) with (storage_type=ustore);
create table t_trigger_des(id1 int, id2 int, id3 int) with (storage_type=ustore);

--step2:创建向src表中插入数据之前对每个语句触发des表插入语句;expect:成功
drop view if exists t_trigger_src_view1;
create view t_trigger_src_view1 as select * from t_trigger_src;
create or replace function tri_insert_func() returns trigger as
$$
declare
begin
insert into t_trigger_des select * from t_trigger_src_view1;
return new;
end
$$ language plpgsql;
/
drop trigger if exists insert_trigger on t_trigger_src_view1 cascade;
create trigger insert_trigger before insert on t_trigger_src_view1
        for each statement
		execute procedure tri_insert_func();
/
create or replace function insert_func()
returns trigger as $$
begin
insert into t_trigger_src
values(new.id1,new.id2,new.id3);
return new;
end;
$$language plpgsql;
/
create trigger insert_trigger_1
instead of insert on t_trigger_src_view1
for each row
execute procedure insert_func();
/
select * from t_trigger_des;
insert into t_trigger_src_view1 values(1,1,1),(2,2,2);
select * from t_trigger_src;
select * from t_trigger_des;

--step3:清理环境;expect:成功
drop table if exists t_trigger_src cascade;
drop table if exists t_trigger_des cascade;
drop view if exists t_trigger_src_view1 cascade;